﻿Imports System.Data.SqlClient
Public Class frmCuentasUnaOld
    Dim mdsDatos As New DataSet
    Dim total As Double
    Private Sub frmCuentasUnaOld_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        CargaCuentas()

    End Sub
    Sub CargaCuentas()
        lstCuentas.Items.Clear()

        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection

        Dim strSql As String = ""

        strSql = "Select Nombre " & _
                                    " From AdeudosTradicionales Order By nombre "

        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader

        Do While rdBuscar.Read()
            lstCuentas.Items.Add(rdBuscar("Nombre").ToString.Trim)
        Loop
    End Sub
    Sub imprime()
        Dim xl As Object
        Dim wb As Object
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        xl.Workbooks.Add()
        wb = xl.activeworkbook
        Dim rng As Integer
        Dim i As Integer

        rng = 6

        wb.SHEETS(1).cells(1, 1).value = "Imprimiendo..."
        wb.SHEETS(1).columns(1).columnwidth = 15



        Dim cuentausuario As String = BuscarStrDatoCitra("Empleados", "CuentaCW", "Codigo='" & frmModuloEmpleadosDetalle.txtCodigo.Text.Trim & "'")
        cuentausuario = Mid(cuentausuario, 5)
        'Dim mes As Date = Now()
        Dim cuentacompac As String = BuscarStrDatoContabilidad("Cuentas", "Codigo", "nombre='" & Me.lstCuentas.Text.Trim & "'")
        cuentacompac = Mid(cuentacompac, 1, 4)
        Dim codigo As String = cuentacompac & cuentausuario
        Dim idcuenta As Integer = BuscarIntDatoContabilidad("cuentas", "id", "codigo='" & codigo & "'")
        Dim tipo As String = "Adeudo" 'BuscarStrDato("NOM20001", "tipo", "nombre='" & Me.lstCuentas.Text.Trim & "'")

        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = ContabilidadConnection

        Dim strSql As String = ""

        strSql = "select *"
        strSql = strSql & " from MovimientosPoliza"
        strSql = strSql & " where idcuenta=" & idcuenta
        strSql = strSql & " order by fecha"


        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader

        wb.SHEETS(1).cells(rng, 1).value = "Nombre: " & frmModuloEmpleadosDetalle.txtPaterno.Text.Trim & " " & frmModuloEmpleadosDetalle.txtMaterno.Text.Trim & " " & frmModuloEmpleadosDetalle.txtNombre.Text.Trim
        wb.SHEETS(1).cells(rng, 1).font.size = 12
        wb.SHEETS(1).cells(rng, 1).font.bold = -1
        rng = rng + 1
        wb.SHEETS(1).cells(rng, 1).value = tipo & " " & Me.lstCuentas.Text.Trim
        wb.SHEETS(1).cells(rng, 1).font.size = 9
        wb.SHEETS(1).cells(rng, 1).font.bold = -1
        rng = rng + 2
        wb.SHEETS(1).cells(rng, 1).value = "Fecha"
        wb.SHEETS(1).cells(rng, 2).value = "Concepto"
        wb.SHEETS(1).cells(rng, 3).value = "Referencia"
        wb.SHEETS(1).cells(rng, 4).value = "Cargos"
        wb.SHEETS(1).cells(rng, 5).value = "Abonos"
        wb.SHEETS(1).cells(rng, 6).value = "Saldo"
        For i = 1 To 6
            wb.SHEETS(1).cells(rng, i).font.size = 9
            wb.SHEETS(1).cells(rng, i).font.bold = -1
        Next
        rng = rng + 1
        rng = rng + 1

        Dim TipoMov As Boolean
        Dim JalaSaldo As Double
        Dim importe As Double = 0
        Dim cargo As Double = 0
        Dim abono As Double = 0
        Dim Concepto As String
        Dim SaldoCargo As Double
        Dim SaldoAbono As Double
        Dim elPrimero As Boolean = False

        Do While rdBuscar.Read()
            TipoMov = rdBuscar("TipoMovto")
            If elPrimero = True Then
                wb.SHEETS(1).cells(rng, 2).value = "Saldos Acumulados"
                wb.SHEETS(1).cells(rng, 4).value = SaldoCargo
                wb.SHEETS(1).cells(rng, 5).value = SaldoAbono
                If tipo = "Adeudo" Then
                    JalaSaldo = SaldoCargo - SaldoAbono
                Else
                    JalaSaldo = SaldoCargo + SaldoAbono
                End If

                rng = rng + 1
                elPrimero = False

            End If
            wb.SHEETS(1).cells(rng, 1).value = rdBuscar("fecha")
            Concepto = rdBuscar("Concepto").ToString.Trim
            If Concepto = "" Then
                wb.SHEETS(1).cells(rng, 2).value = "Saldos Iniciales/Ajuste"
            Else
                wb.SHEETS(1).cells(rng, 2).value = rdBuscar("Concepto").ToString.Trim
            End If

            wb.SHEETS(1).cells(rng, 3).value = rdBuscar("Referencia").ToString.Trim

            importe = rdBuscar("importe")
            If TipoMov = False Then
                wb.SHEETS(1).cells(rng, 4).value = importe
                If tipo.Trim = "Adeudo" Then
                    JalaSaldo = JalaSaldo + importe
                Else
                    JalaSaldo = JalaSaldo - importe
                End If

                cargo = cargo + importe
            Else
                wb.SHEETS(1).cells(rng, 5).value = importe
                If tipo.Trim = "Adeudo" Then
                    JalaSaldo = JalaSaldo - importe
                Else
                    JalaSaldo = JalaSaldo + importe
                End If

                abono = abono + importe
            End If
            wb.SHEETS(1).cells(rng, 6).value = JalaSaldo
            For i = 1 To 6
                wb.SHEETS(1).cells(rng, i).font.size = 9
            Next
            rng = rng + 1
        Loop
        wb.SHEETS(1).cells(rng, 3).value = "Totales"
        wb.SHEETS(1).cells(rng, 4).value = cargo
        wb.SHEETS(1).cells(rng, 5).value = abono
        wb.SHEETS(1).cells(rng, 6).value = JalaSaldo
        For i = 1 To 6
            wb.SHEETS(1).cells(rng, i).font.size = 9
            wb.SHEETS(1).cells(rng, i).font.bold = True
        Next
        rng = rng + 2
        wb.SHEETS(1).cells(rng, 3).value = "Total " & tipo & ": "
        wb.SHEETS(1).cells(rng, 6).value = JalaSaldo
        For i = 1 To 6
            wb.SHEETS(1).cells(rng, i).font.size = 11
            wb.SHEETS(1).cells(rng, i).font.bold = True
        Next

        wb.SHEETS(1).columns(4).numberformat = "#,##0.00"
        wb.SHEETS(1).columns(5).numberformat = "#,##0.00"
        wb.SHEETS(1).columns(6).numberformat = "#,##0.00"

        wb.SHEETS(1).COLUMNS(1).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(2).ColumnWidth = 25
        wb.SHEETS(1).COLUMNS(3).ColumnWidth = 15
        wb.SHEETS(1).COLUMNS(4).ColumnWidth = 15
        wb.SHEETS(1).COLUMNS(5).ColumnWidth = 15
        wb.SHEETS(1).COLUMNS(6).ColumnWidth = 15

        wb.SHEETS(1).cells(1, 1).value = EmpresaActiva
        wb.SHEETS(1).cells(1, 1).FONT.SIZE = 14
        wb.SHEETS(1).cells(2, 1).value = "Informe de Descuentos. Filtro: Nombre: " & frmModuloEmpleadosDetalle.txtPaterno.Text.Trim & " " & frmModuloEmpleadosDetalle.txtMaterno.Text.Trim & " " & frmModuloEmpleadosDetalle.txtNombre.Text.Trim

        wb.SHEETS(1).cells(2, 1).FONT.SIZE = 10
        wb.SHEETS(1).cells(3, 1).value = "Descuento: " & Me.lstCuentas.Text
        wb.SHEETS(1).cells(3, 1).FONT.SIZE = 10
        wb.SHEETS(1).cells(4, 1).value = "Impreso por: " & usuarioactivo & " el " & Now().Day & "-" & Now().Month & "-" & Now().Year
        wb.SHEETS(1).cells(4, 1).FONT.SIZE = 8

    End Sub

    Private Sub btnAbrir_Click(sender As System.Object, e As System.EventArgs) Handles btnAbrir.Click
        imprime()
        Me.Close()
    End Sub

    Private Sub RadButton2_Click(sender As System.Object, e As System.EventArgs) Handles RadButton2.Click
        Close()

    End Sub

End Class